<?php
use \DateTime;
class System
{
    const TAG_TABLE_COL = 'tags.name';
    const TABLE = 'details';

    //加總金額
    public static function sum(array $data)
    {
        $total = 0;
        foreach ($data as $row)
        {
            $total += $row['gold'];
        }
        return $total;
    }
    
    //加總備註金額
    public static function sumNote(array $data)
    {
        $total = 0;
        foreach ($data as $row)
        {
            if (preg_match_all('/\$(\d+)/', $row['note'], $m))
            {
                array_map(function($val) use(&$total){
                    $total += $val;
                }, $m[1]);
            }
        }
        return $total;
    }
    
    //取得單筆資料
    public static function get($id)
    {
        $id = (int)$id;
        $sql = "SELECT * FROM ".self::TABLE." WHERE id={$id} LIMIT 1";
        $retval = array();
        db_PDO::eachRow(function($row) use(&$retval){
            $retval[] = $row;
        }, db_PDO::query(db_PDO::getConn(), $sql));
        return current($retval);
    }

    //查詢
    public static function find(&$date_start=null, &$date_ned=null, array $tags=null)
    {
        $start = $date_start?new DateTime($date_start) : new DateTime(date('Y-m-1'));
        $date_start = $start;
        $end =  $date_ned?new DateTime($date_ned) : new DateTime($start->format('Y-m-').date('t', $start->getTimestamp()));
        $date_ned = $end;
        $sql = "
            SELECT * 
            FROM ".self::TABLE." 
            WHERE date BETWEEN '{$start->format('Y-m-j')}' AND '{$end->format('Y-m-j')}'
            ORDER BY date DESC
      "; //var_dump($sql, func_get_args());
        $retval = array();
        db_PDO::eachRow(function($row) use(&$retval, $tags){
            $match_tags = array_intersect(TagsManager::parse($row['tags']), $tags);
            if (empty($tags) || ! empty($match_tags))
            {
                $retval[] = $row;
            }
        }, db_PDO::query(db_PDO::getConn(), $sql));
        return $retval;
    }

    //新增
    public static function add(array $source)
    {
        $source['date'] = preg_match('/\d{4}-\d{1,2}-\d{1,2}/', $source['date'])?$source['date']:null;
        $date = new DateTime($source['date']);
        $tags = TagsManager::parse($source['tags']);
        $gold = abs((int)$source['gold']);
        $subject = $source['subject']?:'無標題';
        $note = $source['note'];
        $sql_format = "INSERT INTO ".self::TABLE." (date, tags, gold, subject, note) VALUES ('%s', '%s', '%s', '%s', '%s')";
        $sql = sprintf($sql_format,
               $date->format('Y-m-j'),
               join(',', $tags),
               $gold,
               $subject,
               $note
        );
        db_PDO::query(db_PDO::getConn(), $sql);
        $tag_ctrl = TagsManager::instance(self::TAG_TABLE_COL);
        $tag_ctrl->add($tags);
    }
    
    //編輯
    public static function edit($id, array $source)
    {
        $id = (int)$id;
        $source['date'] = preg_match('/\d{4}-\d{1,2}-\d{1,2}/', $source['date'])?$source['date']:null;
        $date = new DateTime($source['date']);
        $tags = TagsManager::parse($source['tags']);
        $gold = abs((int)$source['gold']);
        $subject = $source['subject']?:'無標題';
        $note = $source['note'];
        $sql_format = "UPDATE ".self::TABLE." SET date='%s', tags='%s', gold='%s', subject='%s', note='%s' WHERE id={$id}";
        $sql = sprintf($sql_format,
               $date->format('Y-m-j'),
               join(',', $tags),
               $gold,
               $subject,
               $note
        );//var_dump($sql);
        db_PDO::query(db_PDO::getConn(), $sql);
        $tag_ctrl = TagsManager::instance(self::TAG_TABLE_COL);
        $tag_ctrl->add($tags);
    }
    
    //刪除
    public static function del($id)
    {
        $id = (int)$id;
        $sql = "DELETE FROM ".self::TABLE." WHERE id={$id}";
        db_PDO::query(db_PDO::getConn(), $sql);
    }
}